package com.css.gdzj.gdzjbxgd.modules.userinfo.repository;

import com.css.gdzj.gdzjbxgd.modules.userinfo.repository.entity.UserInfoEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author z
 * @date 2020-12-11
 */
public interface UserInfoRepository extends JpaRepository<UserInfoEntity, String> {
    @Transactional
    @Modifying
    @Query(value = "update user_info set DEL_FLAG= :delFlag where USER_ID = :userId", nativeQuery = true)
    void updateUserState(@Param("delFlag") String delFlag, @Param("userId") String userId);

    @Query(value = "SELECT DISTINCT(CREATE_USER),USER_POST,USER_NAME,USER_UNIT,LOGIN_NAME,LOGIN_PASSWORD,USER_LEVEL," +
            "USER_RANK,USER_REPORT_FLAG,USER_PHONE,USER_ID,USER_POLITICAL_OUTLOOK,USER_OFFICE_PHONE,USER_LEADER_POST," +
            "DEL_FLAG,USER_IS_REPORT " +
            "FROM ((SELECT report_id,create_user,report_year FROM bxgd_report_info) AS a  " +
            "LEFT JOIN user_info b ON a.create_user = b.USER_ID) " +
            "WHERE a.CREATE_USER IN (SELECT USER_ID FROM user_info ) " +
            "and a.report_year = :reportYear " +
            "and decode(userUnit,userUnit!='',user_unit =:userUnit,1 = 1 ) " +
            "and user_level = '0' and user_report_flag = '1' ", nativeQuery = true)
    Page<UserInfoEntity> getIsReportP(@Param("reportYear") String reportYear,
                                      @Param("userUnit") String userUnit, Pageable pageable);

    @Query(value = "SELECT * " +
            "FROM" +
            "  user_info " +
            "WHERE decode(userUnit,userUnit!='',user_unit =:userUnit,1 = 1  ) " +
            "and user_id NOT IN " +
            "  (SELECT " +
            "    create_user " +
            "  FROM" +
            "    bxgd_report_info " +
            "  WHERE report_year = :reportYear) and user_level = '0' and user_report_flag = '1' ", nativeQuery = true)
    Page<UserInfoEntity> getNotReportP(@Param("reportYear") String reportYear,
                                       @Param("userUnit") String userUnit, Pageable pageable);

    @Query(value = "SELECT DISTINCT(CREATE_USER),USER_POST,USER_NAME,USER_UNIT,LOGIN_NAME,LOGIN_PASSWORD,USER_LEVEL," +
            "USER_RANK,USER_REPORT_FLAG,USER_PHONE,USER_ID,USER_POLITICAL_OUTLOOK,USER_OFFICE_PHONE,USER_LEADER_POST," +
            "DEL_FLAG,USER_IS_REPORT " +
            "FROM ((SELECT report_id,create_user,report_year FROM bxgd_report_info) AS a  " +
            "LEFT JOIN user_info b ON a.create_user = b.USER_ID) " +
            "WHERE a.CREATE_USER IN (SELECT USER_ID FROM user_info ) " +
            "and a.report_year = :reportYear " +
            "and if(:userUnit != '',user_unit = :userUnit ,1 = 1 ) " +
            "and if(:loginName != '',login_name like CONCAT('%',:loginName,'%') ,1 = 1 ) " +
            "and if(:userName != '',user_name like CONCAT('%',:userName,'%') ,1 = 1 ) " +
            "and if(:userPoliticalOutlook != '',user_political_outlook = :userPoliticalOutlook ,1 = 1 ) " +
            "and if(:userRank != '',user_Rank = :userRank ,1 = 1 ) " +
            "and if(:userLeaderPost != '',user_leader_post = :userLeaderPost ,1 = 1) " +
            "and user_level = '0' and user_report_flag = '1' ", nativeQuery = true)
    Page<UserInfoEntity> getIsReportPage(@Param("userUnit") String userUnit,
                                         @Param("reportYear") String reportYear,
                                         @Param("loginName") String loginName,
                                         @Param("userName") String userName,
                                         @Param("userPoliticalOutlook") String userPoliticalOutlook,
                                         @Param("userRank") String userRank,//职级
                                         @Param("userLeaderPost") String userLeaderPost,//领导职务
                                         Pageable pageable);

}
